 -- EXEC sp_system_parameters_setup
 /*
  This Proc is the initial setup for the System Parameters. This needs to done for every new database setup.
  This Inserts all the System Parameters for the process types FAB, Wafer Store, Sort, Die Bank, Assembly, 
  Test, Class Store, Mark & Pack, Finished Goods.
  This can be run for the Existing database also.
  If any new parameter is added for any Process, The corresponding insert statement should be written.
 
  This proc also Inserts the Units of Mesurment which we are not using as of now. But it will be used in Future for all 
  the validations to be done.
 	
 */
 ALTER     PROC sp_system_parameters_setup AS
 BEGIN
 	IF EXISTS ( select * from dbo.sysobjects where id = object_id(N'[dbo].[SYSTEM_PARAMETER_TABLE]') and OBJECTPROPERTY(id, N'IsUserTable') = 1 )
 	BEGIN
 		DROP TABLE [dbo].[SYSTEM_PARAMETER_TABLE]
 	END
 
 	PRINT 'DELETED SYSTEM_PARAMETER_TABLE'
 	
 	-- CREATE THE NEW TABLE 
 	CREATE TABLE SYSTEM_PARAMETER_TABLE (
 	PROCESS_TYPE varchar(50), PROCESS_PARAMETER varchar(50), PARAMETER_UNITS varchar(50), PARAMETER_VALUE varchar(50) )
 
 	PRINT 'CREATED SYSTEM_PARAMETER_TABLE'
 
 	IF EXISTS ( select * from dbo.sysobjects where id = object_id(N'[dbo].[Units_Of_Mesurment]') and OBJECTPROPERTY(id, N'IsUserTable') = 1 )
 	BEGIN
 		DROP TABLE [dbo].[Units_Of_Mesurment]
 	END
 
 	PRINT 'DELETED Units_Of_Mesurment'
 
 	CREATE TABLE Units_Of_Mesurment ( UOM varchar(50) NOT NULL, BASEUOM varchar(50) NOT NULL, MAXVALUE varchar(15), MINVALUE varchar(15), ISREQUIRED bit NOT NULL, CONSTRAINT [PK_Units_Of_Mesurment] PRIMARY KEY CLUSTERED ( UOM ) ON [PRIMARY] )
 
 	PRINT 'CREATED Units_Of_Mesurment'
 	
 	-- INSERT THE FAB SYSTEM PARAMETERS
 	INSERT INTO SYSTEM_PARAMETER_TABLE( PROCESS_TYPE, PROCESS_PARAMETER, PARAMETER_UNITS, PARAMETER_VALUE)
 	VALUES( 'FAB', 'MRB Limit', 'UNITS', '')
 
 	INSERT INTO SYSTEM_PARAMETER_TABLE( PROCESS_TYPE, PROCESS_PARAMETER, PARAMETER_UNITS, PARAMETER_VALUE)
 	VALUES( 'FAB', 'Gross Die/Wafer', 'UNITS', '0')
 
 	INSERT INTO SYSTEM_PARAMETER_TABLE( PROCESS_TYPE, PROCESS_PARAMETER, PARAMETER_UNITS, PARAMETER_VALUE)
 	VALUES( 'FAB', 'Standard Unit Cost', '$', '0.00')
 
 	INSERT INTO SYSTEM_PARAMETER_TABLE( PROCESS_TYPE, PROCESS_PARAMETER, PARAMETER_UNITS, PARAMETER_VALUE)
 	VALUES( 'FAB', 'RMA Limit', 'UNITS', '')
 
 	INSERT INTO SYSTEM_PARAMETER_TABLE( PROCESS_TYPE, PROCESS_PARAMETER, PARAMETER_UNITS, PARAMETER_VALUE)
 	VALUES( 'FAB', 'Target Yield', '%', '0.00')
 
 	PRINT 'INSERTED FAB PARAMETERS'
 
 	-- INSERT THE WAFER STORE SYSTEM PARAMETERS
 	INSERT INTO SYSTEM_PARAMETER_TABLE( PROCESS_TYPE, PROCESS_PARAMETER, PARAMETER_UNITS, PARAMETER_VALUE)
 	VALUES( 'Wafer Store', 'Min Level', 'UNITS', '0')
 
 	INSERT INTO SYSTEM_PARAMETER_TABLE( PROCESS_TYPE, PROCESS_PARAMETER, PARAMETER_UNITS, PARAMETER_VALUE)
 	VALUES( 'Wafer Store', 'Max Level', 'UNITS', '0')
 
 	INSERT INTO SYSTEM_PARAMETER_TABLE( PROCESS_TYPE, PROCESS_PARAMETER, PARAMETER_UNITS, PARAMETER_VALUE)
 	VALUES( 'Wafer Store', 'Max Time', 'DAYS', '0')
 
 	PRINT 'INSERTED Wafer Store PARAMETERS'
 
 	-- INSERT THE SORT SYSTEM PARAMETERS
 	INSERT INTO SYSTEM_PARAMETER_TABLE( PROCESS_TYPE, PROCESS_PARAMETER, PARAMETER_UNITS, PARAMETER_VALUE)
 	VALUES( 'SORT', 'Expected Net Die/Wafer', 'UNITS', '0')
 
 	INSERT INTO SYSTEM_PARAMETER_TABLE( PROCESS_TYPE, PROCESS_PARAMETER, PARAMETER_UNITS, PARAMETER_VALUE)
 	VALUES( 'SORT', 'MRB Limit', 'UNITS', '0.00')
 
 	INSERT INTO SYSTEM_PARAMETER_TABLE( PROCESS_TYPE, PROCESS_PARAMETER, PARAMETER_UNITS, PARAMETER_VALUE)
 	VALUES( 'SORT', 'RMA Limit', 'UNITS', '0.00')
 
 	INSERT INTO SYSTEM_PARAMETER_TABLE( PROCESS_TYPE, PROCESS_PARAMETER, PARAMETER_UNITS, PARAMETER_VALUE)
 	VALUES( 'SORT', 'Standard Unit Cost', '$', '0.00')
 
 	INSERT INTO SYSTEM_PARAMETER_TABLE( PROCESS_TYPE, PROCESS_PARAMETER, PARAMETER_UNITS, PARAMETER_VALUE)
 	VALUES( 'SORT', 'Probe Card No', 'TEXT', '')
 
 	INSERT INTO SYSTEM_PARAMETER_TABLE( PROCESS_TYPE, PROCESS_PARAMETER, PARAMETER_UNITS, PARAMETER_VALUE)
 	VALUES( 'SORT', 'System', 'TEXT', '')

 	PRINT 'INSERTED SORT PARAMETERS'
 
 	-- INSERT THE DIEBANK SYSTEM PARAMETERS
 	INSERT INTO SYSTEM_PARAMETER_TABLE( PROCESS_TYPE, PROCESS_PARAMETER, PARAMETER_UNITS, PARAMETER_VALUE)
 	VALUES( 'Die Bank', 'Min Level', 'UNITS', '0')
 
 	INSERT INTO SYSTEM_PARAMETER_TABLE( PROCESS_TYPE, PROCESS_PARAMETER, PARAMETER_UNITS, PARAMETER_VALUE)
 	VALUES( 'Die Bank', 'Max Level', 'UNITS', '0')
 
 	INSERT INTO SYSTEM_PARAMETER_TABLE( PROCESS_TYPE, PROCESS_PARAMETER, PARAMETER_UNITS, PARAMETER_VALUE)
 	VALUES( 'Die Bank', 'Max Time', 'DAYS', '0')
 
 	PRINT 'INSERTED DIE BANK PARAMETERS'
 
 	-- INSERT THE ASSEMBLY SYSTEM PARAMETERS
 	INSERT INTO SYSTEM_PARAMETER_TABLE( PROCESS_TYPE, PROCESS_PARAMETER, PARAMETER_UNITS, PARAMETER_VALUE)
 	VALUES( 'Assembly', 'Package ID', 'TEXT', '')
 
 	INSERT INTO SYSTEM_PARAMETER_TABLE( PROCESS_TYPE, PROCESS_PARAMETER, PARAMETER_UNITS, PARAMETER_VALUE)
 	VALUES( 'Assembly', 'Standard Unit Cost', '$', '0.00')
 
 	INSERT INTO SYSTEM_PARAMETER_TABLE( PROCESS_TYPE, PROCESS_PARAMETER, PARAMETER_UNITS, PARAMETER_VALUE)
 	VALUES( 'Assembly', 'RMA Limit', 'UNITS', '')
 
 	INSERT INTO SYSTEM_PARAMETER_TABLE( PROCESS_TYPE, PROCESS_PARAMETER, PARAMETER_UNITS, PARAMETER_VALUE)
 	VALUES( 'Assembly', 'Target Yield', '%', '0.00')
 
 	PRINT 'INSERTED ASSEMBLY PARAMETERS'
 
 	-- INSERT THE TEST SYSTEM PARAMETERS
 	INSERT INTO SYSTEM_PARAMETER_TABLE( PROCESS_TYPE, PROCESS_PARAMETER, PARAMETER_UNITS, PARAMETER_VALUE)
 	VALUES( 'TEST', 'Target Yield', '%', '0.00')
 
 	INSERT INTO SYSTEM_PARAMETER_TABLE( PROCESS_TYPE, PROCESS_PARAMETER, PARAMETER_UNITS, PARAMETER_VALUE)
 	VALUES( 'TEST', 'MRB Limit', 'UNITS', '')
 
 	INSERT INTO SYSTEM_PARAMETER_TABLE( PROCESS_TYPE, PROCESS_PARAMETER, PARAMETER_UNITS, PARAMETER_VALUE)
 	VALUES( 'TEST', 'RMA Limit', 'UNITS', '')
 
 	INSERT INTO SYSTEM_PARAMETER_TABLE( PROCESS_TYPE, PROCESS_PARAMETER, PARAMETER_UNITS, PARAMETER_VALUE)
 	VALUES( 'TEST', 'Standard Unit Cost', '$', '0.00')
 
 	INSERT INTO SYSTEM_PARAMETER_TABLE( PROCESS_TYPE, PROCESS_PARAMETER, PARAMETER_UNITS, PARAMETER_VALUE)
 	VALUES( 'TEST', 'Test Jig', 'TEXT', '')
 
 	INSERT INTO SYSTEM_PARAMETER_TABLE( PROCESS_TYPE, PROCESS_PARAMETER, PARAMETER_UNITS, PARAMETER_VALUE)
 	VALUES( 'TEST', 'System', 'TEXT', '')
 
 	PRINT 'INSERTED TEST PARAMETERS'
 
 	-- INSERT THE CLASS STORE SYSTEM PARAMETERS
 	INSERT INTO SYSTEM_PARAMETER_TABLE( PROCESS_TYPE, PROCESS_PARAMETER, PARAMETER_UNITS, PARAMETER_VALUE)
 	VALUES( 'Class Store', 'Min Level', 'UNITS', '0')
 
 	INSERT INTO SYSTEM_PARAMETER_TABLE( PROCESS_TYPE, PROCESS_PARAMETER, PARAMETER_UNITS, PARAMETER_VALUE)
 	VALUES( 'Class Store', 'Max Level', 'UNITS', '0')
 
 	INSERT INTO SYSTEM_PARAMETER_TABLE( PROCESS_TYPE, PROCESS_PARAMETER, PARAMETER_UNITS, PARAMETER_VALUE)
 	VALUES( 'Class Store', 'Max Time', 'DAYS', '0')
 
 	PRINT 'INSERTED CLASS STORE PARAMETERS'
 
 	-- INSERT THE MARK AND PACK SYSTEM PARAMETERS
         
 	INSERT INTO SYSTEM_PARAMETER_TABLE( PROCESS_TYPE, PROCESS_PARAMETER, PARAMETER_UNITS, PARAMETER_VALUE)
 	VALUES( 'Mark & Pack', 'Target Yield', '%', '0.00')
 
 	INSERT INTO SYSTEM_PARAMETER_TABLE( PROCESS_TYPE, PROCESS_PARAMETER, PARAMETER_UNITS, PARAMETER_VALUE)
 	VALUES( 'Mark & Pack', 'Standard Unit Cost', '$', '0.00')
 
 	INSERT INTO SYSTEM_PARAMETER_TABLE( PROCESS_TYPE, PROCESS_PARAMETER, PARAMETER_UNITS, PARAMETER_VALUE)
 	VALUES( 'Mark & Pack', 'RMA Limit', '%', '0.00')
 
 	INSERT INTO SYSTEM_PARAMETER_TABLE( PROCESS_TYPE, PROCESS_PARAMETER, PARAMETER_UNITS, PARAMETER_VALUE)
 	VALUES( 'Mark & Pack', 'Visual System', 'TEXT', '')
 
 	INSERT INTO SYSTEM_PARAMETER_TABLE( PROCESS_TYPE, PROCESS_PARAMETER, PARAMETER_UNITS, PARAMETER_VALUE)
 	VALUES( 'Mark & Pack', 'Packing Method', 'TEXT', '')
 
 	PRINT 'INSERTED MARK AND PACK PARAMETERS'
 
 	-- INSERT THE FINISHED GOODS SYSTEM PARAMETERS
 	INSERT INTO SYSTEM_PARAMETER_TABLE( PROCESS_TYPE, PROCESS_PARAMETER, PARAMETER_UNITS, PARAMETER_VALUE)
 	VALUES( 'Finished Goods', 'Min Level', 'UNITS', '0')
 
 	INSERT INTO SYSTEM_PARAMETER_TABLE( PROCESS_TYPE, PROCESS_PARAMETER, PARAMETER_UNITS, PARAMETER_VALUE)
 	VALUES( 'Finished Goods', 'Max Level', 'UNITS', '0')
 
 	INSERT INTO SYSTEM_PARAMETER_TABLE( PROCESS_TYPE, PROCESS_PARAMETER, PARAMETER_UNITS, PARAMETER_VALUE)
 	VALUES( 'Finished Goods', 'Reorder Level', 'UNITS', '0')
 
 	INSERT INTO SYSTEM_PARAMETER_TABLE( PROCESS_TYPE, PROCESS_PARAMETER, PARAMETER_UNITS, PARAMETER_VALUE)
 	VALUES( 'Finished Goods', 'Units Per Tray', 'UNITS', '0')
 
 	INSERT INTO SYSTEM_PARAMETER_TABLE( PROCESS_TYPE, PROCESS_PARAMETER, PARAMETER_UNITS, PARAMETER_VALUE)
 	VALUES( 'Finished Goods', 'Trays Per InnerBox', 'UNITS', '0')
 
 	INSERT INTO SYSTEM_PARAMETER_TABLE( PROCESS_TYPE, PROCESS_PARAMETER, PARAMETER_UNITS, PARAMETER_VALUE)
 	VALUES( 'Finished Goods', 'InnerBoxes Per OuterBox', 'UNITS', '0')
 
 	INSERT INTO SYSTEM_PARAMETER_TABLE( PROCESS_TYPE, PROCESS_PARAMETER, PARAMETER_UNITS, PARAMETER_VALUE)
 	VALUES( 'Finished Goods', 'Harmonized Traffic Code', 'TEXT', '')
 
 	INSERT INTO SYSTEM_PARAMETER_TABLE( PROCESS_TYPE, PROCESS_PARAMETER, PARAMETER_UNITS, PARAMETER_VALUE)
 	VALUES( 'Finished Goods', 'License Exception', 'TEXT', '')
 
 	INSERT INTO SYSTEM_PARAMETER_TABLE( PROCESS_TYPE, PROCESS_PARAMETER, PARAMETER_UNITS, PARAMETER_VALUE)
 	VALUES( 'Finished Goods', 'ECCN', 'TEXT', '')
 
 	INSERT INTO SYSTEM_PARAMETER_TABLE( PROCESS_TYPE, PROCESS_PARAMETER, PARAMETER_UNITS, PARAMETER_VALUE)
 	VALUES( 'Finished Goods', 'UOM', 'UNITS', '1')
 
 	PRINT 'INSERTED FINISHED GOODS PARAMETERS'
 
 	-- INSERT THE UOM 
 	INSERT INTO Units_Of_Mesurment( UOM, BASEUOM, MAXVALUE, MINVALUE, ISREQUIRED )VALUES( '%', 'DOUBLE', '100.00', '0.00', 0 )
 	INSERT INTO Units_Of_Mesurment( UOM, BASEUOM, MAXVALUE, MINVALUE, ISREQUIRED )VALUES( 'UNITS', 'INT', '999999', '0', 0 )
 	INSERT INTO Units_Of_Mesurment( UOM, BASEUOM, MAXVALUE, MINVALUE, ISREQUIRED )VALUES( '$', 'DOUBLE', '9999999999.99', '0.00', 0 )
 	INSERT INTO Units_Of_Mesurment( UOM, BASEUOM, MAXVALUE, MINVALUE, ISREQUIRED )VALUES( 'DAYS', 'INT', '999999', '0', 0 )
 	INSERT INTO Units_Of_Mesurment( UOM, BASEUOM, MAXVALUE, MINVALUE, ISREQUIRED )VALUES( 'TEXT', 'STRING', '100', '0', 0 )
 	INSERT INTO Units_Of_Mesurment( UOM, BASEUOM, MAXVALUE, MINVALUE, ISREQUIRED )VALUES( 'INCH^2', 'DOUBLE', '100', '0.00', 0 )
 
 	-- UPDATE THE PREVIOUS PROCESS TYPES
 
 	DECLARE @processTypesCursor CURSOR
 	DECLARE @parameterCursor CURSOR
 	DECLARE @processTypeId INT
 	DECLARE @baseProcessType VARCHAR(50)
 	DECLARE @parameterName VARCHAR(50)
 	DECLARE @parameterUnits VARCHAR(50)
 	DECLARE @parameterValue VARCHAR(50)
 
 	SET @processTypesCursor = CURSOR FOR SELECT ProcessTypeId, Base_Process_Type FROM Process_Types_Table
 	OPEN @processTypesCursor
 	FETCH NEXT FROM @processTypesCursor INTO @processTypeId, @baseProcessType
 	WHILE @@FETCH_STATUS = 0
 	BEGIN
 		SET @parameterCursor = CURSOR FOR SELECT PROCESS_PARAMETER, PARAMETER_UNITS, PARAMETER_VALUE FROM SYSTEM_PARAMETER_TABLE 
 		WHERE PROCESS_PARAMETER NOT IN ( SELECT DPP_Parameter FROM Default_Process_Parameter_Table WHERE ProcessTypeId = @processTypeId ) 
 		AND PROCESS_TYPE = @baseProcessType
 		OPEN @parameterCursor
 		FETCH NEXT FROM @parameterCursor INTO @parameterName, @parameterUnits, @parameterValue
 		WHILE @@FETCH_STATUS = 0
 		BEGIN
			PRINT 'I am here'
 			INSERT INTO Default_Process_Parameter_Table( ProcessTypeId, DPP_Parameter, DPP_Parameter_value, DPP_Parameter_Units, DPP_System_Parameter )
 			VALUES( @processTypeId, @parameterName, @parameterValue, @parameterUnits, 0)
 			FETCH NEXT FROM @parameterCursor INTO @parameterName, @parameterUnits, @parameterValue
 		END
 		CLOSE @parameterCursor
 		DEALLOCATE @parameterCursor
 
 		FETCH NEXT FROM @processTypesCursor INTO @processTypeId, @baseProcessType
 	END
 	CLOSE @processTypesCursor
 	DEALLOCATE @processTypesCursor
 
 
 	DECLARE @processStepId INT
 	DECLARE @processType VARCHAR(50)
 	DECLARE @prevProcessType VARCHAR(50)
 
 	DECLARE @rowCount INT
 
 	DECLARE @processStepCursor CURSOR
 
 	SET @processStepCursor = CURSOR FOR SELECT PROCESS_STEP.PROCESSSTEP_ID, PS_TYPE_NAME FROM PROCESS_STEP, PRODUCT WHERE PROCESS_STEP.PROCESSSTEP_ID = PRODUCT.PROCESSSTEP_ID AND P_TYPE IS NULL ORDER BY PS_TYPE_NAME	
 	
 	OPEN @processStepCursor
 	FETCH NEXT FROM @processStepCursor INTO @processStepId, @processType
 	WHILE @@FETCH_STATUS = 0
 	BEGIN
 		SET @parameterCursor = CURSOR FOR SELECT PROCESS_PARAMETER, PARAMETER_UNITS, PARAMETER_VALUE FROM SYSTEM_PARAMETER_TABLE WHERE PROCESS_TYPE = 'FAB'
 		OPEN @parameterCursor
 		FETCH NEXT FROM @parameterCursor INTO @parameterName, @parameterUnits, @parameterValue
 	
 		WHILE @@FETCH_STATUS = 0
 		BEGIN
 			SELECT @rowCount = COUNT(*) FROM PROCESS_PARAMETER WHERE PROCESSSTEP_ID = @processStepId AND PARAMETER_KEY = @parameterName
 			
 			IF @rowCount = 0
 			BEGIN
 				INSERT INTO Process_Parameter( ProcessStep_ID, Parameter_Key, Parameter_Units, Parameter_Value )
 				VALUES( @processStepId, @parameterName, @parameterUnits, @parameterValue )
 			END
 			
 			FETCH NEXT FROM @parameterCursor INTO @parameterName, @parameterUnits, @parameterValue
 		END
 		CLOSE @parameterCursor		
 		DEALLOCATE @parameterCursor
 		FETCH NEXT FROM @processStepCursor INTO @processStepId, @processType
 	END
 
 	CLOSE @processStepCursor
 	DEALLOCATE @processStepCursor
 	
 END
